SQL21天自学通.pdf

目录目录.......................................................................................................................................... 1译者的话 ............................................................................................................................. 14第一周概貌............................................................................................................................. 16从这里开始............................................................................................................. 16第一天 SQL 简介.................................................................................................................. 17SQL 简史................................................................................................................ 17数据库简史............................................................................................................. 17设计数据库的结构................................................................................................... 21SQL 总览......................................................................................................... 23流行的 SQL 开发工具....................................................................................... 24SQL 在编程中的应用........................................................................................ 27第二天 查询 — — SELECT 语句的使用 ................................................................................... 30目标 .................................................................................................................... 30背景................................................................................................................. 30一般的语法规则............................................................................................... 30你的第一个查询............................................................................................... 33总结 .................................................................................................................... 37问与答 ................................................................................................................. 38校练场.................................................................................................................... 38练习 .................................................................................................................... 39第三天 表达式 条件语句与运算.......................................................................................... 40第四天 函数 对数据的进一步处理....................................................................................... 60目标 .................................................................................................................... 60汇总函数................................................................................................................. 60COUNT............................................................................................................ 61SUM ................................................................................................................ 61AVG................................................................................................................. 63MAX................................................................................................................ 63MIN................................................................................................................. 64VARIANCE...................................................................................................... 65STDDEV.......................................................................................................... 66SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 2日期/时间函数......................................................................................................... 66ADD_MONTHS................................................................................................ 67LAST_DAY...................................................................................................... 68MONTHS_BETWEEN....................................................................................... 69NEW_TIME...................................................................................................... 70NEXT_DAY...................................................................................................... 71SYSDATE......................................................................................................... 72数学函数................................................................................................................. 72ABS ................................................................................................................. 73CEIL 和 FLOOR ............................................................................................... 73COS COSH SIN SINH TAN TANH........................................................ 73EXP................................................................................................................. 75LN and LOG..................................................................................................... 75MOD................................................................................................................ 76POWER............................................................................................................ 77SIGN ............................................................................................................... 77SQRT ............................................................................................................... 78字符函数................................................................................................................. 79CHR................................................................................................................. 79CONCAT .......................................................................................................... 79INITCAP.......................................................................................................... 80LOWER 和 UPPER ........................................................................................... 81LPAD 与 RPAD................................................................................................. 82LTRIM 与 RTRIM............................................................................................. 83REPLACE ........................................................................................................ 84SUBSTR........................................................................................................... 85TRANSLATE.................................................................................................... 88INSTR............................................................................................................. 88LENGTH.......................................................................................................... 89转换函数................................................................................................................. 89TO_CHAR........................................................................................................ 90TO_NUMBER................................................................................................... 91其它函数................................................................................................................. 91GREATEST 与 LEAST...................................................................................... 91USER............................................................................................................... 92总结........................................................................................................................ 92问与答.................................................................................................................... 93校练场.................................................................................................................... 93练习........................................................................................................................ 94第五天 SQL 中的子句........................................................................................................... 95SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 3目标 .................................................................................................................... 95WHERE 子句 .......................................................................................................... 96STARTING WITH子句............................................................................................ 98ORDER BY 子句 ..................................................................................................... 99GROUP BY 子句 ....................................................................................................104HAVING 子句.........................................................................................................109子句的综合应用.....................................................................................................112总结.......................................................................................................................117问与答...................................................................................................................117校练场...................................................................................................................117练习.......................................................................................................................118第六天 表的联合..................................................................................................................119介绍.......................................................................................................................119在一个 SELECT 语句中使用多个表.........................................................................119正确地找到列..................................................................................................123等值联合................................................................................................................124不等值联合............................................................................................................129外部联合与内部联合..............................................................................................130表的自我联合.........................................................................................................132总结.......................................................................................................................134问与答...................................................................................................................134校练场...................................................................................................................134练习.......................................................................................................................135第七天 子查询 内嵌的 SQL 子句........................................................................................136目标 ...................................................................................................................136建立一个子查询.....................................................................................................136在子查询中使用汇总函数.......................................................................................140子查询的嵌套.........................................................................................................141相关子查询............................................................................................................144EXISTS ANY ALL 的使用.................................................................................147SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 4总结.......................................................................................................................151问与答...................................................................................................................151校练场...................................................................................................................152练习 ...................................................................................................................153第一周回顾............................................................................................................................154预览.......................................................................................................................154第二周概貌............................................................................................................................155这一周都讲些什么..................................................................................................155第八天 操作数据..................................................................................................................156目标.......................................................................................................................156数据操作语句.........................................................................................................156插入语句................................................................................................................157INSERT VALUES 语句.....................................................................................157INSERT SELECT 语句.....................................................................................161UPDATE语句 ........................................................................................................163DELETE 语句.........................................................................................................166从外部数据源中导入和导出数据.............................................................................169Microsoft Access..............................................................................................170Microsoft and Sybase SQL Server ......................................................................171Personal Oracle7...............................................................................................171总结.......................................................................................................................172问与答...................................................................................................................172校练场...................................................................................................................173练习.......................................................................................................................173第九天 创建和操作表...........................................................................................................174目标 ...................................................................................................................174CREATE DATABASE 语句......................................................................................174建立数据库时的选项.......................................................................................175设计数据库.....................................................................................................176建立数据字典..................................................................................................176建立关键字段..................................................................................................177CREATE TABLE 语句.............................................................................................178表名................................................................................................................179SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 5FIRST NAME..................................................................................................179空值属性.........................................................................................................180唯一属性.........................................................................................................181表的存储与尺寸的调整....................................................................................183用一个已经存在的表来建表.............................................................................184ALTER TABLE 语句...............................................................................................185DROP TABLE 语句.................................................................................................186DROP DATABASE语句..........................................................................................187总结.......................................................................................................................188问与答...................................................................................................................188校练场...................................................................................................................189练习.......................................................................................................................190第 10 天 创建视图和索引.......................................................................................................191目标.......................................................................................................................191使用视图................................................................................................................192列的重命名.....................................................................................................196SQL 对视图的处理过程...................................................................................197在 SELECT 语句使用约束................................................................................201在视图中修改数据...........................................................................................201在视图中修改数据的几个问题.........................................................................203通用应用程序的视图.......................................................................................204删除视图语句..................................................................................................207使用索引................................................................................................................207什么是索引 ..................................................................................................207使用索引的技巧..............................................................................................212对更多的字段进行索引....................................................................................212在创建索引时使用 UNIQUE 关键字.................................................................214索引与归并.....................................................................................................216群集 簇 的使用...........................................................................................217总结.......................................................................................................................218问与答 ................................................................................................................219校练场...................................................................................................................219练习 ...................................................................................................................220第 11 天 事务处理控制.........................................................................................................221目标 ...................................................................................................................221事务控制................................................................................................................221SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 6银行应用程序..................................................................................................222开始事务处理..................................................................................................223结束事务处理..................................................................................................225取消事务处理..................................................................................................228在事务中使用保存点.......................................................................................231总结.......................................................................................................................234问与答...................................................................................................................234校练场...................................................................................................................235练习.......................................................................................................................235第 12 天 数据库安全............................................................................................................236前提 数据库管理员..............................................................................................236流行的数据库产品与安全.......................................................................................237如何让一个数据库变得安全....................................................................................237Personal Oracle7 与安全..........................................................................................238创建用户.........................................................................................................238创建角色.........................................................................................................240用户权限.........................................................................................................242为安全的目的而使用视图................................................................................247总结.......................................................................................................................251问与答...................................................................................................................252校练场...................................................................................................................252练习.......................................................................................................................253第 13 天 高级 SQL.................................................................................................................254目标 ...................................................................................................................254临时表...................................................................................................................254Title ................................................................................................................257游标.......................................................................................................................259创建游标.........................................................................................................260打开游标.........................................................................................................260使用游标来进行翻阅.......................................................................................261测试游标的状态..............................................................................................262关闭游标.........................................................................................................263游标的适用范围..............................................................................................264创建和使用存贮过程..............................................................................................265在存贮过程中使用参数....................................................................................267SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 7删除一个存贮过程...........................................................................................269存贮过程的嵌套..............................................................................................270设计和使用触发机制..............................................................................................272触发机制与事务处理.......................................................................................273使用触发机制时的限制....................................................................................275触发机制的嵌套..............................................................................................275在选择语句中使用更新和删除................................................................................275在执行前测试选择语句....................................................................................276嵌入型 SQL............................................................................................................277静态 SQL 与动态 SQL.....................................................................................277使用 SQL 来编程....................................................................................................279总结.......................................................................................................................280问与答...................................................................................................................280校练场...................................................................................................................280练习.......................................................................................................................281第 14 天 动态使用 SQL ........................................................................................................282目标.......................................................................................................................282快速入门................................................................................................................282ODBC .............................................................................................................282Personal Oracle 7..............................................................................................283InterBase SQL ISQL ...................................................................................283Visual C  ......................................................................................................284Delphi.............................................................................................................284设置.......................................................................................................................284创建数据库............................................................................................................285使用 MS QUERY 来完成链接..................................................................................290将 VISUAL C 与 SQL 结合使用 ...........................................................................292将 DELPHI 与 SQL 结合使用..................................................................................296总结.......................................................................................................................302问与答...................................................................................................................303校练场...................................................................................................................303练习.......................................................................................................................303第二周回顾............................................................................................................................304第三周概貌............................................................................................................................305SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 8应用你对 SQL 的知识.............................................................................................305第 15 天 对 SQL 语句优化以提高其性能...............................................................................306目标.......................................................................................................................306让你的 SQL 语句更易读.........................................................................................307全表扫描................................................................................................................308加入一个新的索引..................................................................................................309在查询中各个元素的布局.......................................................................................309过程................................................................................................................311避免使用 OR...................................................................................................311OLAP 与 OLTP 的比较...........................................................................................313OLTP 的调试...................................................................................................313OLAP 的调试..................................................................................................314批量载入与事务处理进程.......................................................................................314删除索引以优化数据的载入....................................................................................316经常使用 COMMIT 来让 DBA 走开 ........................................................................316在动态环境中重新生成表和索引.............................................................................317数据库的调整.........................................................................................................319性能的障碍............................................................................................................322内置的调整工具.....................................................................................................323总结.......................................................................................................................323问与答...................................................................................................................324校练场...................................................................................................................324练习.......................................................................................................................324第 16 天 用视图从数据字典中获得信息................................................................................326目标.......................................................................................................................326数据字典简介.........................................................................................................326用户的数据字典.....................................................................................................327数据字典中的内容..................................................................................................327Oracle 的数据字典...........................................................................................328Sybase 的数据字典..........................................................................................328ORACLE 数据字典的内部结构 ...............................................................................328用户视图.........................................................................................................328SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 9系统数据库管理员视图....................................................................................336数据库对象.....................................................................................................339数据库的生长..................................................................................................343动态执行视图..................................................................................................347总结.......................................................................................................................349问与答...................................................................................................................349校练场...................................................................................................................350练习.......................................................................................................................350第 17 天 使用 SQL 来生成 SQL 语句 ....................................................................................351目标.......................................................................................................................351使用 SQL 来生成 SQL 语句的目的..........................................................................351几个 SQL*PLUS 命令.............................................................................................352SET ECHO ON/OFF.........................................................................................353SET FEEDBACK ON/OFF................................................................................353SET HEADING ON/OFF ..................................................................................353SPOOL FILENAME/OFF..................................................................................353START FILENAME..........................................................................................354ED FILENAME................................................................................................354计算所有的表中的行数...........................................................................................354为多个用户赋予系统权限.......................................................................................359将你的表的权限赋予其它的用户.............................................................................361在载入数据时解除对数的约束................................................................................363一次创建多个同义字..............................................................................................364为你的表创建视图..................................................................................................368在一个计划中清除其所有的表的内容......................................................................369使用 SQL 来生成 SHELL 脚本................................................................................371再建表和索引.........................................................................................................372总结.......................................................................................................................373问与答...................................................................................................................373校练场...................................................................................................................373练习.......................................................................................................................374第 18 天 PL/SQL 简介..........................................................................................................376目标.......................................................................................................................376SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 10入门.......................................................................................................................376在 PL/SQL 中的数据类型........................................................................................377字符串类型.....................................................................................................377数值数据类型..................................................................................................378二进制数据类型.....................................................................................................378日期数据类型.........................................................................................................378逻辑数据类型.........................................................................................................378ROWID...........................................................................................................379PL/SQL 块的结构...................................................................................................379注释.......................................................................................................................380DECLARE 部分......................................................................................................380变量声明.........................................................................................................380常量定义.........................................................................................................381指针定义.........................................................................................................381%TYPE 属性...................................................................................................382%ROWTYPE 属性...........................................................................................382%ROWCOUNT 属性........................................................................................383Procdure 部分.........................................................................................................383BEGIN … … END..............................................................................................383指针控制命令..................................................................................................384条件语句.........................................................................................................386LOOPS 循环....................................................................................................387EXCEPTION 部分..................................................................................................390激活 EXCEPTION 异常 .............................................................................390异常的处理.....................................................................................................391将输入返回给用户..................................................................................................392在 PL/SQL 中的事务控制........................................................................................393让所有的事在一起工作....................................................................................394示例表及数据..................................................................................................394一个简单的 PL/SQL 语句块.............................................................................395又一个程序.....................................................................................................398存储过程 包和触发机制.......................................................................................403总结.......................................................................................................................406问与答...................................................................................................................407校练场...................................................................................................................407练习.......................................................................................................................407SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 11第 19 天 TRANSACT-SQL 简介............................................................................................408目标.......................................................................................................................408TRANSACT-SQL 概貌............................................................................................408对 ANSI SQL 的扩展..............................................................................................408谁需要使用 TRANSACT-SQL..........................................................................409TRANSACT-SQL 的基本组件..........................................................................409数据类型................................................................................................................409使用 TRANSACT-SQL 来访问数据库......................................................................411BASEBALL 数据库 .........................................................................................411定义局部变量..................................................................................................414定义全局变量..................................................................................................414使用变量.........................................................................................................415PRINT 命令 ....................................................................................................417流控制...................................................................................................................417BEGIN … … END 语句......................................................................................418IF … … ELSE 语句 ............................................................................................418EXIST 条件.....................................................................................................421WHILE 循环....................................................................................................422使用 WHILE 循环在表中翻阅..........................................................................424TRANSACT-SQL 中的通配符.................................................................................426使用 COMPUTE 来生成摘要报告............................................................................426日期转换................................................................................................................427SQL SERVER 的诊断工具 — — SET 命令..................................................................427总结.......................................................................................................................428问与答...................................................................................................................428校练场...................................................................................................................429练习.......................................................................................................................429第 20 天 SQL*PLUS .............................................................................................................430目标.......................................................................................................................430简介.......................................................................................................................430SQL*PLUS 缓存.....................................................................................................430DESCRIBE 命令.....................................................................................................435SHOW 命令............................................................................................................436文件命令................................................................................................................438SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 12SAVE GET EDIT 命令................................................................................438运行一个文件..................................................................................................439查询的假脱机输出...........................................................................................440SET 命令 ...............................................................................................................442LOGIN.SQL 文件 ...................................................................................................445CLEAR 命令 ..........................................................................................................446将你的输出格式化..................................................................................................446TTITLE 与 BTITLE..........................................................................................446格式化列 COLUMN HEADING FORMAT ..............................................447报表与分类汇总.....................................................................................................449BREAK ON.....................................................................................................449COMPUTE......................................................................................................450在 SQL*PLUS 中使用变量......................................................................................453DEFINE ..........................................................................................................454ACCEPT .........................................................................................................455NEW_VALUE..................................................................................................457DUAL 表........................................................................................................458DECODE 函数.................................................................................................459日期转换................................................................................................................462运行一系列的 SQL 文件.........................................................................................465在你的 SQL 脚本中加入注释..................................................................................466高级报表................................................................................................................467总结.......................................................................................................................469问与答...................................................................................................................469校练场...................................................................................................................469练习.......................................................................................................................470第 21 天 常见的 SQL 错误及解决方法 ..................................................................................471目标 ...................................................................................................................471介绍.......................................................................................................................471常见的错误............................................................................................................471Table or View Does Not Exist.............................................................................471Invalid Username or Password ...........................................................................472FROM Keyword Not Specified...........................................................................473Group Function Is Not Allowed Here ..................................................................474Invalid Column Name........................................................................................475SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 13Missing Keyword .............................................................................................475Missing Left Parenthesis....................................................................................476Missing Right Parenthesis..................................................................................477Missing Comma................................................................................................478Column Ambiguously Defined ...........................................................................478Not Enough Arguments for Function...................................................................480Not Enough Values............................................................................................481Integrity Constraint Violated--Parent Key Not Found ............................................482Oracle Not Available .........................................................................................483Inserted Value Too Large for Column ..................................................................483TNS:listener Could Not Resolve SID Given in Connect Descriptor.........................484Insufficient Privileges During Grants...................................................................484Escape Character in Your Statement--Invalid Character .........................................485Cannot Create Operating System File ..................................................................485Common Logical Mistakes.................................................................................485Using Reserved Words in Your SQL statement.....................................................486The Use of DISTINCT When Selecting Multiple Columns.....................................487Dropping an Unqualified Table ...........................................................................487The Use of Public Synonyms in a Multischema Database.......................................488The Dreaded Cartesian Product ..........................................................................488Failure to Enforce File System Structure Conventions ...........................................489Allowing Large Tables to Take Default Storage Parameters....................................489Placing Objects in the System Tablespace............................................................490Failure to Compress Large Backup Files ..............................................................491Failure to Budget System Resources ...................................................................491Preventing Problems with Your Data...................................................................491Searching for Duplicate Records in Your Database................................................491总结 ...................................................................................................................491校练场...................................................................................................................492练习.......................................................................................................................492第三周回顾............................................................................................................................494附件 A 在 SQL 中的常见术语...............................................................................................495ALTER DATABASE.........................................................................................495ALTER USER..................................................................................................495BEGIN TRANSACTION ..................................................................................495CLOSE CURSOR.............................................................................................495COMMIT TRANSACTION...............................................................................496CREATE DATABASE.......................................................................................496CREATE INDEX..............................................................................................496CREATE PROCEDURE....................................................................................496CREATE TABLE..............................................................................................497CREATE TRIGGER..........................................................................................497SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 14CREATE USER................................................................................................497CREATE VIEW................................................................................................497DEALLOCATE CURSOR.................................................................................498DROP DATABASE...........................................................................................498DROP INDEX..................................................................................................498DROP PROCEDURE........................................................................................498DROP TABLE..................................................................................................498DROP TRIGGER .............................................................................................499DROP VIEW....................................................................................................499EXECUTE.......................................................................................................499FETCH............................................................................................................499FROM.............................................................................................................499GRANT...........................................................................................................500GROUP BY.....................................................................................................500HAVING..........................................................................................................500INTERSECT....................................................................................................500ORDER BY.....................................................................................................500ROLLBACK TRANSACTION ..........................................................................500REVOKE.........................................................................................................500SELECT..........................................................................................................501SET TRANSACTION.......................................................................................501UNION............................................................................................................501WHERE..........................................................................................................501*.....................................................................................................................501附件 B 在第 14 天中的 C 源代码清单 ................................................................................502附件 C 第 14 天中的 Delphi 源代码清单...............................................................................521附件 D 参考内容..................................................................................................................524书..........................................................................................................................524Developing Sybase Applications ..................................................................524Sybase Developer's Guide ...........................................................................524Microsoft SQL Server 6.5 Unleashed, 2E ......................................................524Teach Yourself Delphi in 21 Days ................................................................524Delphi Developer's Guide ...........................................................................524Delphi Programming Unleashed ..................................................................525Essential Oracle 7.2 ...................................................................................525Developing Personal Oracle7 for Windows 95 Applications ............................525Teach Yourself C  Programming in 21 Days ...............................................525Teach Yourself Tansact-SQL in 21 Days .......................................................525Teach Yourself PL/SQL in 21 Days ............................................................525杂志 ...................................................................................................................526DBMS.............................................................................................................526Oracle Magazine...............................................................................................526SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 15SQL 的互联网资源.................................................................................................526附件 E ACSLL 码表 .............................................................................................................527附件 F 问题与练习答案........................................................................................................533第一天 SQL 简介.................................................................................................533问题答案.........................................................................................................533练习答案.........................................................................................................533第二天 查询 — — SELECT 语句的使用 ..................................................................533问题答案.........................................................................................................533练习答案.........................................................................................................534第三天 表达式 条件语句与运算.........................................................................535问题答案.........................................................................................................535练习答案.........................................................................................................535第四天 函数 对获得数据的进一步处理...............................................................536问题答案.........................................................................................................536练习答案.........................................................................................................537第五天 SQL 中的子句..........................................................................................538问题答案.........................................................................................................538练习答案.........................................................................................................538第六天 表的联接..................................................................................................540问题答案.........................................................................................................540练习答案.........................................................................................................541第 7 天 子查询 内嵌的 SELECT 语句 ............................................................542问题答案.........................................................................................................542练习答案.........................................................................................................544第八天 操作数据..................................................................................................544问题答案.........................................................................................................544练习答案.........................................................................................................546第九天 创建和操作表...........................................................................................546问题答案.........................................................................................................546练习答案.........................................................................................................548第 10 天 创建视图和索引.......................................................................................549问题答案.........................................................................................................549练习答案 .....................................................................................................550第 11 天 事务处理控制.........................................................................................550问题答案.........................................................................................................550练习答案.........................................................................................................551第 12 天 数据库安全............................................................................................552SQL 21 日自学通(V1.0) 翻译人 笨猪EMAIL wyhsillypig@163.com 16问题答案.........................................................................................................552练习答案.........................................................................................................552第 13 天 高级 SQL.................................................................................................553问题答案.........................................................................................................553练习答案.........................................................................................................553第 14 天 动态使用 SQL ........................................................................................554问题答案.........................................................................................................554练习答案.........................................................................................................554第 15 天 对 SQL 语句优化以提高其性能...............................................................555问题答案.........................................................................................................555练习答案.........................................................................................................555第 16 天 用视图从数据字典中获得信息................................................................557问题答案.........................................................................................................557练习答案.........................................................................................................557第 17 天 使用 SQL 来生成 SQL 语句 ....................................................................558问题答案.........................................................................................................558练习答案.........................................................................................................560第 18 天 PL/SQL 简介..........................................................................................561问题答案.........................................................................................................561练习答案.........................................................................................................561第 19 天 TRANSACT-SQL 简介............................................................................562问题答案.........................................................................................................562练习答案.........................................................................................................562第 20 天 SQL*PLUS .............................................................................................563问题答案.........................................................................................................563练习答案.........................................................................................................563第 21 天 常见的 SQL 错误及解决方法 ..................................................................564问题答案.........................................................................................................564练习答案.........................................................................................................565